/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package jhu.bookstore;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author jowens
 */
public class DBHelper {

    public static String CONN_STRING = "jdbc:derby://localhost:1527/bookstore_collection_db";
    //public static String host = "jdbc:mysql://localhost:3306/bookstore_collection_db";
    public static String USERNAME = "app";
    public static String PASSWORD = "pass";
    public static String GET_BOOKS = "SELECT * FROM BOOKS";
    public static String GET_USERS = "SELECT * FROM USERS";
    public static String User_count = "SELECT COUNT(*) FROM USERS";
    public static String Auth_User_Count = "SELECT COUNT(*) FROM USERS ";
    private Connection conn = null;
    private boolean connected = false;

    public boolean connect() {
        try {
            //Class.forName("com.mysql.jdbc.Driver");
            //conn = DriverManager.getConnection(host, "root", "pass");
            conn = DriverManager.getConnection(CONN_STRING, USERNAME, PASSWORD);
            connected = true;
        } catch (SQLException ex) {
            Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return connected;
    }

    public void close() {
        if (connected) {
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
            connected = false;
        }
    }

    public boolean isConnected() {
        return connected;
    }

    public List<Book> getBooks() {
        List<Book> bookList = new ArrayList<Book>();
        if (!connected) {
            return bookList;
        }
        try {
            System.err.println("conn = " + conn);
            System.err.println("isConnected = " + connected);

            PreparedStatement stmt = conn.prepareStatement(GET_BOOKS);
            stmt.execute();
            ResultSet rs = stmt.getResultSet();

            while (rs.next()) {
                int bookId = rs.getInt(1);
                String bookName = rs.getString(2);
                String author = rs.getString(3);
                String category = rs.getString(4);
                double price = rs.getDouble(5);
                String synopsis = rs.getString(6);
                Book book = new Book(bookId, author, bookName, category, price, synopsis);
                bookList.add(book);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return bookList;
    }

    public List<User> getUsers() {
        List<User> userList = new ArrayList<User>();
        if (!connected) {
            return userList;
        }
        try {
            System.err.println("conn = " + conn);
            System.err.println("isConnected = " + connected);

            PreparedStatement stmt = conn.prepareStatement(GET_USERS);
            stmt.execute();
            ResultSet rs = stmt.getResultSet();

            while (rs.next()) {
                int uid = rs.getInt(1);
                String userName = rs.getString(2);
                String password = rs.getString(3);
                User user = new User(uid, userName, password);
                userList.add(user);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return userList;
    }

    public boolean addUser(User u) {
        boolean updateOk = true;
        try {
            String query = "INSERT INTO USERS VALUES("
                    + //u.getUserId() + ",'" + u.getUserName() + "','" + u.getPassword() + "', CURRENT_TIMESTAMP)";
                    u.getId() + ",'" + u.getName() + "','" + u.getPassword() + "', CURRENT_TIMESTAMP)";
            PreparedStatement stmt = conn.prepareStatement(query);
            stmt.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
            updateOk = false;
        }
        return updateOk;
    }

    public boolean authenticateUser(User u) {
        boolean authenticated = false;
        int userCount = 0;
        try {
            String query = "SELECT COUNT(*) as \"COUNT\" FROM USERS "
                    + "WHERE \"Username\" = '" + u.getName()
                    + "' and \"Password\" = '" + u.getPassword() + "'";
            PreparedStatement stmt = conn.prepareStatement(query);
            stmt.execute();
            ResultSet rs = stmt.getResultSet();

            while (rs.next()) {
                userCount = rs.getInt(1);
            }
            System.err.println(userCount);

            if (userCount > 0) {
                authenticated = true;
            } else {
                authenticated = false;
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return authenticated;
    }

    public int getUserCount() {
        int userCount = 0;
        boolean updateOk = true;
        try {
            PreparedStatement stmt = conn.prepareStatement(User_count);
            stmt.execute();
            ResultSet rs = stmt.getResultSet();

            while (rs.next()) {
                userCount = rs.getInt(1);
            }

            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
            updateOk = false;
        }
        return userCount;
    }

    public boolean makeSale(int bookId, int userId, int quantity) {
        boolean updateOk = true;
        try {
            System.err.println("bookId = " + bookId + " userId = " + userId + " quantity = " + quantity);
            String query = "INSERT INTO SALES VALUES(" + bookId + "," + userId + "," + quantity + ", CURRENT_TIMESTAMP)";
            System.err.println(query);
            PreparedStatement stmt = conn.prepareStatement(query);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DBHelper.class.getName()).log(Level.SEVERE, null, ex);
            updateOk = false;
        }
        return updateOk;

    }
}
